SQL Serverの実行計画からクエリとパラメーターをSQLで取得する

code:select_query_and_parameter.sql

-- A5:SQL Mk-2で疑似命令を使う時に指定する型 / query_planのParameterList/ColumnlReferenceで取得するときの型

-- 文字列型 String / ParameterDataType="nvarchar(4000)" ParameterCompiledValue="N'AAA'"

-- 整数型 Integer / ParameterDataType="int" ParameterCompiledValue="(1)"

-- 実数型 Float /

-- 論理値型 Boolean /

-- 日付型 Date / '2019-10-26' date

-- 時刻型 Time /

-- 日付／時刻型 DateTime / NULL datetime2(7)

SELECT

TOP 100

--eqp.query_plan, -- 実行計画が欲しいときはここのコメントアウトを外す

--est.text, -- クエリが欲しいときはここのコメントアウトを外す(変数をバインドする前なので「(@P0 nvarchar(4000)) SELECT * FROM SAMPLE_TABLE WHERE id = @P1;」みたいな状態)

IsNull(REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (

CAST(

item.query(

'<tmp>

{

for $i in ColumnReference

return <t>{string($i/@Column)}BLANK_FOR_REPLACE{string($i/@ParameterCompiledValue)}BLANK_FOR_REPLACEString</t>

}

</tmp>'

)

AS nvarchar(MAX))

, '</t><t>@', CONCAT(CHAR(13), CHAR(10), '--*SetParameter ')), '</t></tmp>', CONCAT(CHAR(13), CHAR(10))), 'BLANK_FOR_REPLACE', ' '), ' N''', ' '''), '<tmp><t>@', '--*SetParameter '), '<tmp/>', ''), '')

AS '--a5m2parameter'

, CONCAT(CHAR(13), CHAR(10), REPLACE (REPLACE (REPLACE (SUBSTRING (

est.text

, (eqs.statement_start_offset / 2) + 1

, ((

CASE eqs.statement_end_offset

WHEN - 1

THEN DATALENGTH(est.text)

ELSE eqs.statement_end_offset

END - eqs.statement_start_offset

) / 2) + 1

), CHAR (13), ' '), CHAR (10), ' '), CHAR (9), ' '), ';')

AS 'text'

, CONCAT(CHAR(13), CHAR(10), '-- execution_count:', eqs.execution_count) AS execution_count

, CONCAT(CHAR(13), CHAR(10), '-- last_exection_time:'

, FORMAT(eqs.last_execution_time, 'yyyy/MM/dd HH:mm:ss.fff')

, CHAR(13), CHAR(10)) AS last_execution_time

FROM

sys.dm_exec_query_stats AS eqs

OUTER APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS est

OUTER APPLY sys.dm_exec_query_plan(eqs.plan_handle) AS eqp

OUTER APPLY sys.dm_exec_text_query_plan(

eqs.plan_handle

, eqs.statement_start_offset

, eqs.statement_end_offset

) AS etqp

OUTER APPLY(

values (

TRY_CONVERT(

XML

, SUBSTRING(

etqp.query_plan

, CHARINDEX('<ParameterList>', etqp.query_plan)

, CHARINDEX('</ParameterList>', etqp.query_plan)

+ LEN('</ParameterList>')

- CHARINDEX('<ParameterList>', etqp.query_plan)

)

)

)

) AS tempTable(xml)

OUTER APPLY xml.nodes('/ParameterList') as T(item)

WHERE

-- FETCH API_CURSORは取得しない

query_hash <> 0x0000000000000000

ORDER BY

last_execution_time DESC

;

やりたいこと

動かしてみた環境

出力結果

table:result

--a5m2parameter text execution_count last_execution_time

--*SetParameter P1 '' String<br>(略) <br>SELECT * (略) <br>-- exe(略) <br>-- last_exe(略)

この行をコピペするとこう。改行されている

code:sql

--*SetParameter P1 '' String

--*SetParameter P2 'test' String

SELECT * FROM SAMPLE_TABLE WHERE id = @P1 AND name = @P2;

-- execution_count:1

-- last_exection_time:2019/07/10 12:12:39.697

カラム内およびカラムとカラムの間で改行を突っ込んでる

履歴

(2020/10/21)

(2020/11/11)